<?php
/**
 * Created by PhpStorm.
 * User: jjj
 * Date: 2018/1/16
 * Time: 10:32
 */

namespace app\common\model;


use app\admin\model\PageResponse;
use think\Db;
use think\Model;

/**
 * Class SpecialColumn
 * @package app\common\model
 *
 * @property mixed column_id
 * @property mixed column_title
 * @property mixed column_description
 * @property mixed column_cover
 * @property json covers
 * @property mixed column_detail
 * @property mixed column_teacher
 * @property mixed column_price
 * @property mixed column_sales
 * @property mixed column_createtime
 * @property mixed column_updatetime
 * @property mixed column_status
 * @property integer num_subscribed
 * @property float sales_volume
 *
 * @property float apple_pay_price
 * @property string apple_pay_production_id
 */
class SpecialColumn extends Model
{
    protected $table = 'special_column';
    protected $pk = 'column_id';

    protected $field = [
        'column_id',
        'column_title',
        'column_description',
        'column_cover',
        'covers',
        'column_detail',
        'column_teacher',
        'column_price',
        'column_sales',
        'column_createtime',
        'column_updatetime',
        'column_status',
        'column_expiry',
        'apple_pay_price',
        'apple_pay_production_id',
    ];

    const STATUS_ON = 0;
    const STATUS_OFF = 1;
    const STATUS_DELETE = 2;
    private static $StatusNames = [
        self::STATUS_ON     => '正常',
        self::STATUS_OFF    => '下架',
        self::STATUS_DELETE => '删除',
    ];
    public static function getStatusName($status) {
        return self::$StatusNames[$status] ?? null;
    }
    public static function getStatusNames() {
        return self::$StatusNames;
    }

    /**
     * 管理总列表
     *
     * @param int $limit
     * @param int $offset
     * @param int $teacher_id
     * @param int $status
     * @param string|null $keywords
     * @return PageResponse
     */
    public static function manageList(int $limit, int $offset, int $teacher_id = 0, int $status = -1, string $keywords = null) {
        $where = '';
        $params = ['limit'=>$limit,'offset'=>$offset];
        if ($teacher_id > 0) {
            $where .= ' AND sc.column_teacher=:teacher_id ';
            $params['teacher_id'] = $teacher_id;
        }
        if ($keywords) {
            $where .= ' AND sc.column_title LIKE :kw ';
            $params['kw'] = "%{$keywords}%";
        }
        if ($status != -1) {
            $where .= ' AND SC.column_status=:stat ';
            $params['stat'] = $status;
        }

        $sql = <<<SQL
SELECT sc.*, s.sales_title
FROM special_column sc
LEFT JOIN sales s ON sc.column_sales=s.sales_id
WHERE TRUE {$where} 
ORDER BY sc.column_id DESC 
LIMIT :limit OFFSET :offset
SQL;
        $list = Db::query($sql, $params);
        self::manageFormatList($list);

        $sql = <<<SQL
SELECT count(sc.*) num FROM special_column sc WHERE TRUE {$where}
SQL;
        unset($params['limit'],$params['offset']);
        $total = Db::query($sql, $params)[0]['num'];

        return PageResponse::success($list, $total);
    }

    /**
     * 简单列表，用于选择
     *
     * @param int $teacher_id
     * @param string|null $keywords
     * @return mixed
     */
    public static function simpleList(int $teacher_id = 0, string $keywords = null) {
        $where = '';
        $params = ['stat' => self::STATUS_ON];
        if ($teacher_id > 0) {
            $where .= ' AND column_teacher=:teacher_id ';
            $params['teacher_id'] = $teacher_id;
        }
        if ($keywords) {
            $where .= ' AND column_title LIKE :kw ';
            $params['kw'] = "%{$keywords}%";
        }
        $sql = <<<SQL
SELECT 
    column_id as id,
    column_title as title,
    column_price as price,
    t.realname AS teacher_name
FROM special_column JOIN teacher t ON t.id=column_teacher
WHERE column_status=:stat {$where}
ORDER BY column_id DESC 
SQL;
        $list = Db::query($sql, $params);
        return $list;
    }

    /**
     * 修改状态
     *
     * @param $id
     * @param $status
     * @return bool
     */
    public static function setStatus($id, $status) {
        if (key_exists($status, self::$StatusNames) === false) return false;

        $sql = <<<SQL
UPDATE special_column SET column_status=:stat WHERE column_id=:id
SQL;
        Db::query($sql, ['id'=>$id,'stat'=>$status]);

        return true;
    }

    /**
     * (未完成)
     * 统计订阅数
     *
     * @param $id
     */
    public static function statisticsSubscribed($id) {
        $sql = <<<SQL
SELECT 
FROM user_subscribe_special uss 

WHERE uss.ust_special=:id
SQL;

        $sql = <<<SQL
UPDATE special_column SET num_subscribed = (
SELECT count(uss.*) FROM user_subscribe_special uss WHERE uss.ust_special=column_id
) 
WHERE column_id=:id
SQL;
        Db::execute($sql, ['id' => $id]);
    }

    private static function manageFormatList(& $list) {
        foreach ($list as & $item) {
            self::manageFormat($item);
        }
    }

    private static function manageFormat(& $item) {
        if (isset($item['column_status'])) $item['column_status_name'] = self::getStatusName($item['column_status']);
    }

    /**
     * 重写保存，处理json
     *
     * @param array $data
     * @param array $where
     * @param null $sequence
     * @return false|int
     */
    public function save($data = [], $where = [], $sequence = null)
    {
        if (isset($data['covers'])) {
            if (is_array($data['covers']) || is_object($data['covers'])) $data['covers'] = json_encode($data['covers'],JSON_UNESCAPED_UNICODE);
        }

        if (isset($this->covers)) {
            if (is_array($this->covers) || is_object($this->covers)) $this->covers = json_encode($this->covers,JSON_UNESCAPED_UNICODE);
        }

        return parent::save($data, $where, $sequence);
    }

}